﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace OrionBank.Classe.SQLite
{
    class Operations
    {
        static public void Sauve(Classe.Operations o)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_INSERT, SQLite.Sql.GetConnection());
                
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@date", o.Date);
                cmd.Parameters.AddWithValue("@id_mode_paiement", o.IdModePaiement);
                cmd.Parameters.AddWithValue("@tiers", o.Tiers);
                cmd.Parameters.AddWithValue("@libelle", o.Libelle);
                cmd.Parameters.AddWithValue("@id_categories", o.IdCategorie);
                cmd.Parameters.AddWithValue("@montant", o.Montant);
                cmd.Parameters.AddWithValue("@id_compte", o.IdCompte);
                cmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
        }

        static public List<Classe.Operations> ChargeTout(Int32 idC)
        {
            List<Classe.Operations> ls = new List<Classe.Operations>();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_ALL, SQLite.Sql.GetConnection());
                
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idC", idC);
               
                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    ls.Add(Classe.Operations.Charge(rdr.GetInt32(0)));
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
            return ls;
        }

        static public List<String> ChargeToutTiers(Int32 idC)
        {
            List<String> ls = new List<String>();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_ALL_TIERS, SQLite.Sql.GetConnection());
                
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idC", idC);
                
                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    ls.Add(rdr.GetString(0));
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
            return ls;
        }

        static public double CalculAVenir(Int32 idCompte)
        {
            double rPositif = 0.0;
            double rNegatif = 0.0;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_CALCUL_AVENIR, SQLite.Sql.GetConnection());
                
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idCompte", idCompte);
                
                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (rdr.GetString(0).Equals("D"))
                        rNegatif += rdr.GetDouble(1);
                    if (rdr.GetString(0).Equals("C"))
                        rPositif += rdr.GetDouble(1);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
            return rPositif - rNegatif;
        }

        static public double CalculSoldOpePoint(Int32 idCompte)
        {
            double rSoldIni = Classe.SQLite.Compte.Charge(idCompte).SoldeInitial;
            double rPositif = 0.0;
            double rNegatif = 0.0;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_CALCUL_SOLD_OPE_POINT, SQLite.Sql.GetConnection());
                
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idCompte", idCompte);
                
                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (rdr.GetString(0).Equals("D"))
                        rNegatif += rdr.GetDouble(1);
                    if (rdr.GetString(0).Equals("C"))
                        rPositif += rdr.GetDouble(1);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return rSoldIni + rPositif - rNegatif;
        }

        static public void Maj(Classe.Operations o)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_UPDATE_ID, SQLite.Sql.GetConnection());
                
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@date", o.Date);
                cmd.Parameters.AddWithValue("@id_mode_paiement", o.IdModePaiement);
                cmd.Parameters.AddWithValue("@tiers", o.Tiers);
                cmd.Parameters.AddWithValue("@libelle", o.Libelle);
                cmd.Parameters.AddWithValue("@id_categories", o.IdCategorie);
                cmd.Parameters.AddWithValue("@montant", o.Montant);
                cmd.Parameters.AddWithValue("@id_compte", o.IdCompte);
                cmd.Parameters.AddWithValue("@id", o.Id);
                cmd.Parameters.AddWithValue("@date_pointage", o.DatePointage);
                cmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
        }

        static public List<string[]> GroupByTiers(Int32 idC)
        {
            
            List<string[]> ls = new List<string[]>();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_GROUP_BY_TIERS, SQLite.Sql.GetConnection());
                string[] t = new string[2];

                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idCompte", idC);
                
                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    t = new string[2];
                    t[0] = rdr.GetString(0);
                    t[1] = rdr.GetDouble(1).ToString();
                    if (t[0] == null || t[0] == string.Empty)
                        t[0] = "Sans Tiers";
                    ls.Add(t);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return ls;
        }

        static public List<string[]> GroupByTiersDC(Int32 idC)
        {
            
            List<string[]> ls = new List<string[]>();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_GROUP_BY_TIERS_DC, SQLite.Sql.GetConnection());
                string[] t = new string[3];

                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idCompte", idC);

                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    t = new string[3];
                    t[0] = rdr.GetString(0);
                    if (t[0] == null || t[0] == string.Empty)
                        t[0] = "Sans Tiers";
                    t[1] = rdr.GetDouble(1).ToString();
                    t[2] = rdr.GetDouble(2).ToString();
                    ls.Add(t);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
            return ls;
        }

        static public List<string[]> GroupByCategories(Int32 idC)
        {
            List<string[]> ls = new List<string[]>();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_GROUP_BY_CATEGORIES, SQLite.Sql.GetConnection());
                string[] t = new string[2];

                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idCompte", idC);
                
                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    t = new string[2];
                    t[0] = rdr.GetString(0);
                    t[1] = rdr.GetDouble(1).ToString();
                    if (t[0] == null || t[0] == string.Empty)
                        t[0] = "Sans Catégories";
                    ls.Add(t);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return ls;
        }

        static public List<string[]> GroupByCategoriesDC(Int32 idC)
        {
            List<string[]> ls = new List<string[]>();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_GROUP_BY_CATEGORIES_DC, SQLite.Sql.GetConnection());
                string[] t = new string[3];

                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idCompte", idC);
                
                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    t = new string[3];
                    t[0] = rdr.GetString(0);
                    if (t[0] == null || t[0] == string.Empty)
                        t[0] = "Sans Catégories";
                    t[1] = rdr.GetDouble(1).ToString();
                    t[2] = rdr.GetDouble(2).ToString();
                    ls.Add(t);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return ls;
        }

        static public double SoldeCompteAt(DateTime dt, Int32 idC)
        {
            double retour = 0.0;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_SOLDE_DATE, SQLite.Sql.GetConnection());
                string[] t = new string[3];

                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idCompte", idC);
                cmd.Parameters.AddWithValue("@date", dt);

                SQLiteDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    try
                    {
                        if(rdr.GetValue(0).ToString()!= string.Empty)
                            retour = rdr.GetDouble(0);
                    }
                    catch
                    {

                    }
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return retour;
        }

        static public DateTime GetMaxDate(Int32 idC)
        {
            DateTime retour = DateTime.Now;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_MAX_DATE, SQLite.Sql.GetConnection());
                string[] t = new string[3];

                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idCompte", idC);
                
                SQLiteDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    retour = rdr.GetDateTime(0);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return retour;
        }

        static public DateTime GetMinDate(Int32 idC)
        {
            
            DateTime retour = DateTime.Now;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_MIN_DATE, SQLite.Sql.GetConnection());
                string[] t = new string[3];
            
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idCompte", idC);
                
                SQLiteDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    retour = rdr.GetDateTime(0);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return retour;
        }

        static public DataSet ChargeGrilleOperation(Int32 idCompte)
        {
            DataSet retour = new DataSet();
            try
            {
                SQLiteDataAdapter dsOpe = new SQLiteDataAdapter(String.Format(SQLite.Sql.OPERATIONS_CHARGE_GRILLE, idCompte), SQLite.Sql.GetConnection());
                SQLiteCommandBuilder cb = new SQLiteCommandBuilder(dsOpe);
                dsOpe.Fill(retour, "Operations");
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
            return retour;
        }

        static public List<Classe.Operations> ChargeGrilleListeOperation(Int32 idCompte)
        {
            
            List<Classe.Operations> retour = new List<Classe.Operations>();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_CHARGE_LISTE_GRILLE, SQLite.Sql.GetConnection());
                
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@id", idCompte);

                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    retour.Add(Classe.Operations.Charge(rdr.GetInt32(0)));
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return retour;
        }

        static public DataSet ChargeGrilleOperationFiltre(int idCompte,
                                bool bDate, string cbFiltreDate, DateTime txtFiltreDate,
                                bool bModePaiement, string txtFiltreModePaiement,
                                bool bTiers, string txtFiltreTiers,
                                bool bCategorie, string txtFiltreCategorie,
                                bool bMontant, string cbFiltreMontant, double txtFiltreMontant)
        {
            DataSet retour = new DataSet();
            try
            {
                string sql = String.Format(SQLite.Sql.OPERATIONS_CHARGE_GRILLE_FILTRE, idCompte);
                if (bDate)
                    sql += " AND strftime('%Y-%m-%d',o.date)" + cbFiltreDate + "'" + txtFiltreDate.Year + "-" + txtFiltreDate.Month.ToString("00") + "-" + txtFiltreDate.Day.ToString("00") + "'";
                if (bModePaiement)
                    sql += " AND mp.id=" + txtFiltreModePaiement;
                if (bTiers)
                    sql += " AND o.tiers='" + txtFiltreTiers + "'";
                if (bCategorie)
                    sql += " AND ca.id=" + txtFiltreCategorie;
                if (bMontant)
                    sql += " AND o.montant" + cbFiltreMontant + txtFiltreMontant;
                sql += " ORDER BY date_pointage ASC, date ASC";
                SQLiteDataAdapter dsOpe = new SQLiteDataAdapter(sql, SQLite.Sql.GetConnection());
                SQLiteCommandBuilder cb = new SQLiteCommandBuilder(dsOpe);
                dsOpe.Fill(retour, "Operations");
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
            return retour;
        }

        static public Classe.Operations Charge(int id)
        {
            Classe.Operations o = new Classe.Operations();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_ID, SQLite.Sql.GetConnection());
                
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@id", id);

                SQLiteDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    o.Id = rdr.GetInt32(0);
                    o.Date = rdr.GetDateTime(1);
                    try
                    {
                        o.DatePointage = rdr.GetDateTime(7);
                    }
                    catch
                    {
                        o.DatePointage = null;
                    }
                    o.IdCategorie = rdr.GetInt32(5);
                    o.IdCompte = rdr.GetInt32(8);
                    o.IdModePaiement = rdr.GetInt32(2);
                    o.Libelle = rdr.GetString(4);
                    o.Montant = rdr.GetDouble(6);
                    o.Tiers = rdr.GetString(3);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return o;
        }

        static public void Delete(int id)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.OPERATIONS_DELETE_ID, SQLite.Sql.GetConnection());

                cmd.Prepare();
                cmd.Parameters.AddWithValue("@id", id);
                cmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
        }
    }
}